package service;

import Utils.DruidUtils;
import domain.News;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.List;

public class NewsService {
    public static List<News> getNewsByTypeId(Long newsTypeId) {
        List<News> newsList = new ArrayList<>();

        // 操作数据库，在数据库中取值
        try {
            // 注册驱动
            Class.forName("com.mysql.cj.jdbc.Driver");
            // 连接数据库
            String url = "jdbc:mysql://localhost:3306/javawebsql?useUnicode=true&characterEncoding=utf-8";
            Connection con = DriverManager.getConnection(url, "root", "123456");

            String sql = "select n.news_id newsId, n.news_title newsTitle, n.news_date newsDate, n.news_image newsImage, n.news_content newsContent, nt.news_type_name newsTypeName from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id where n.news_type_id=?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            newsList = queryRunner.query(sql, new BeanListHandler<>(News.class));

        } catch (Exception e) {
            System.out.println(e);
        }
        return newsList;
    }

    public static List<News> getNewsByTypeId(Long newsTypeId, Long pageIndex, Long pageSize) {
        List<News> newsList = new ArrayList<>();

        // 操作数据库，在数据库中取值
        try {

            // 编写sql语句
            String sql = "select n.news_id newsId, n.news_title newsTitle, n.news_date newsDate, n.news_image newsImage, n.news_content newsContent, nt.news_type_name newsTypeName from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id where n.news_type_id=? limit ?,?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            newsList = queryRunner.query(sql, new BeanListHandler<>(News.class), newsTypeId, pageIndex, pageSize);

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return newsList;
    }

    public static News getNewsById(Long id){
        // 创建news对象
        News n = new News();

        // 操作数据库，在数据库中取值
        try {
            // 编写sql语句
            String sql = "select n.news_id newsId, n.news_title newsTitle, n.news_date newsDate, n.news_image newsImage, n.news_content newsContent  from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id where n.news_id=?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            n = queryRunner.query(sql, new BeanHandler<>(News.class), id);

        }catch (Exception e){
            throw new RuntimeException(e);
        }
        return n;
    }

    public static List<News> getNewsContentByKeyWord(String keyWord){
        List<News> newsList = new ArrayList<>();
        // 操作数据库，在数据库中取值
        try {

            // 编写sql语句
            String sql = "select n.news_id newsId, n.news_title newsTitle, n.news_date newsDate, n.news_image newsImage, n.news_content newsContent, nt.news_type_name newsTypeName from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id where n.news_title like ?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            newsList = queryRunner.query(sql, new BeanListHandler<>(News.class), "%"+keyWord+"%");

        } catch (Exception e) {
            throw new RuntimeException(e);
        }
        return newsList;
    }

    public static int getNewsTotalByTypeId(Long newsTypeId){
        int total=0;

        // 操作数据库，在数据库中取值
        try {

            // 编写sql语句
            String sql = "select n.news_id as newsId from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id where n.news_type_id=?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            List<News> newsList = queryRunner.query(sql, new BeanListHandler<>(News.class), newsTypeId);

            total = newsList.size();

        } catch (Exception e) {
            System.out.println(e);
        }
        return total;
    }

    public static int getNewsTotalBySearchKeyWord(String keyWord){
        int total=0;

        // 操作数据库，在数据库中取值
        try {

            // 编写sql语句
            String sql = "select n.news_id newsId, n.news_title newsTitle, n.news_date newsDate, n.news_image newsImage, n.news_content newsContent, nt.news_type_name newsTypeName from " +
                    "javawebsql.news n join javawebsql.news_type nt on " +
                    "nt.news_type_id = n.news_type_id where n.news_title like ?;";

            QueryRunner queryRunner = new QueryRunner(DruidUtils.getDataSource());
            List<News> newsList = queryRunner.query(sql, new BeanListHandler<>(News.class), "%"+keyWord+"%");

            total = newsList.size();

        } catch (Exception e) {
            System.out.println(e);
        }
        return total;
    }
}
